package com.sg.service.biz.component.nb;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sg.common.util.CommonFunctionHelper;
import com.sg.dto.biz.component.req.ImplementExcelImportReqDto;
import com.sg.dto.biz.component.req.ImplementImportExcelFileDataReqDto;
import com.sg.dto.biz.component.req.ImplementSmartSessionReceiveFieldInputReqDto;
import com.sg.dto.biz.component.res.ImplementExcelImportRespDto;
import com.sg.dto.biz.component.res.ImplementImportExcelFileDataRespDto;
import com.sg.dto.biz.component.res.ImplementSmartSessionReceiveFieldInputRespDto;
import org.apache.skywalking.apm.toolkit.trace.Tag;
import org.apache.skywalking.apm.toolkit.trace.Tags;
import org.apache.skywalking.apm.toolkit.trace.Trace;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Map;

/**
 * @author ： 手工接入方法
 * @version 1.0
 * @since 2022/5/28 15:32
 */
@Service
public class NbSystemDocking {

    private static Map<String, Object> convertJsonToMap(JSONObject jsonObject) {
        Map<String, Object> map = new HashMap<>();
        for (String key : jsonObject.keySet()) {
            map.put(key, jsonObject.get(key));
        }
        return map;
    }


    /**
     * code:dataParsingWarehousing
     * name:M2执行导入excel文件数据（特殊方法）
     * desc:undefined
     * gen by moon at 3/23/2024, 7:29:45 PM
     **/
    @Trace(operationName = "M2执行导入excel文件数据（特殊方法）")
    @Tags({@Tag(key = "参数", value = "arg[0]"),
            @Tag(key = "返回值", value = "returnedObj")})
    public ImplementImportExcelFileDataRespDto implementImportExcelFileData(ImplementImportExcelFileDataReqDto reqDto) {
        String dynamicTableName = CommonFunctionHelper.humpToUnderline(reqDto.getBusinessObjectCode()); // 表名可能需要进一步转换为符合SQL命名规则的格式
        String url = "jdbc:mysql://" + reqDto.getDatabaseAddress() + ":" + reqDto.getDatabasePort() + "/" + reqDto.getDatabaseName() + "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        JSONArray jsonArray = JSONArray.parseArray(reqDto.getFileContent());
        Map<String, String> fieldMap = new HashMap<>();
        for (BaseMetadataObjectPropertyDto one : reqDto.getBaseMetadataObjectPropertyList()) {
            fieldMap.put(one.getObjectPropertyName(), one.getObjectPropertyCode());
        }
        try (Connection conn = DriverManager.getConnection(url, reqDto.getDatabaseUserId(), reqDto.getDatabasePassWord())) {
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                Map<String, Object> jsonData = convertJsonToMap(jsonObject);
                // 构建SQL插入语句
                String sql = "INSERT INTO " + dynamicTableName + " (";
                StringBuilder fieldsPart = new StringBuilder();
                StringBuilder valuesPart = new StringBuilder("VALUES (");

                for (String chineseName : jsonData.keySet()) {
                    String englishName = fieldMap.get(chineseName);
                    fieldsPart.append(CommonFunctionHelper.humpToUnderline(englishName)).append(", ");
                    valuesPart.append("?").append(", ");
                }

                // 移除尾部的逗号和空格
                fieldsPart = new StringBuilder(fieldsPart.substring(0, fieldsPart.length() - 2));
                valuesPart = new StringBuilder(valuesPart.substring(0, valuesPart.length() - 2));

                // 完成SQL语句的构建
                sql += fieldsPart.toString() + ") " + valuesPart.toString() + ")";

                // 创建PreparedStatement
                try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                    int index = 1;
                    for (String chineseName : jsonData.keySet()) {
                        pstmt.setObject(index++, jsonData.get(chineseName)); // 使用值
                    }

                    // 执行插入操作
                    int affectedRows = pstmt.executeUpdate();
                    if (affectedRows > 0) {
                        System.out.println("数据插入成功！");
                    } else {
                        System.out.println("数据插入失败！");
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ImplementImportExcelFileDataRespDto();
    }

    /**
     * code:excelImport
     * name:M2执行Excel导入（特殊方法）
     * desc:undefined
     * gen by moon at 3/23/2024, 7:30:04 PM
     **/
    @Trace(operationName = "M2执行Excel导入（特殊方法）")
    @Tags({@Tag(key = "参数", value = "arg[0]"),
            @Tag(key = "返回值", value = "returnedObj")})
    public ImplementExcelImportRespDto implementExcelImport(ImplementExcelImportReqDto reqDto) {
        // TODO ruizhe skai dong ; 3/23/2024, 7:30:04 PM
        return new ImplementExcelImportRespDto();
    }

    /**
     * code:receptionService
     * name:M2接收数据库配置出参
     * desc:undefined
     * gen by moon at 3/23/2024, 7:30:17 PM
     **/
    @Trace(operationName = "M2接收数据库配置出参")
    @Tags({@Tag(key = "参数", value = "arg[0]"),
            @Tag(key = "返回值", value = "returnedObj")})
    public ImplementSmartSessionReceiveFieldInputRespDto implementSmartSessionReceiveFieldInput(ImplementSmartSessionReceiveFieldInputReqDto reqDto) {
        return BeanUtil.toBean(reqDto, ImplementSmartSessionReceiveFieldInputRespDto.class);
    }
    //手工接入方法
}
